Selecting ODBC Function

The guidelines in this section help you to selecting which ODBC functions will give you the best performance.

Using SQLPrepare/SQLExecute and SQLExecDirect

Using SQLPrepare/SQLExecute is not always as efficient as using SQLExecDirect. Use SQLExecDirect for queries that will be executed once and SQLPrepare/SQLExecute for queries that will be executed multiple times.

ODBC drivers are optimized based on the perceived use of the functions that are being executed. SQLPrepare/SQLExecute is optimized for multiple executions of statements that use parameter markers. SQLExecDirect is optimized for a single execution of a SQL statement. Unfortunately, more than 75% of all ODBC applications use SQLPrepare/SQLExecute exclusively.

Consider an ODBC driver that implements SQLPrepare by creating a stored procedure on the server which contains the prepared statement. Creating stored procedures involves substantial overhead, but the statement can be executed multiple times. Although creating stored procedures is performance-expensive, processing is minimal because the query is parsed and optimization paths are stored at create procedure time.

Using SQLPrepare/SQLExecute for a statement that is executed only once results in unnecessary overhead. Furthermore, applications that use SQLPrepare/SQLExecute for large single execution query batches exhibit poor performance. Similarly, applications that always use SQLExecDirect do not perform as well as those that use a logical combination of SQLPrepare/SQLExecute and SQLExecDirect sequences.

Using Arrays of Parameters

Passing arrays of parameter values for bulk insert operations, for example, with SQLPrepare/SQLExecute and SQLExecDirect can reduce the ODBC call load and network traffic. To use arrays of parameters, the application calls SQLSetStmtAttr with the following attribute arguments:

SQL_ATTR_PARAMSET_SIZE sets the array size of the parameter.

SQL_ATTR_PARAMS_PROCESSED_PTR assigns a variable filled by SQLExecute, which contains the number of rows that are actually inserted.

SQL_ATTR_PARAM_STATUS_PTR points to an array in which status information for each row of parameter values is returned.

NOTE: With ODBC 3.x, calls to SQLSetStmtAttr with the SQL_ATTR_PARAMSET_SIZE, SQL_ATTR_PARAMS_PROCESSED_ARRAY, and SQL_ATTR_PARAM_STATUS_PTR arguments replace the ODBC 2.x call to SQLParamOptions.

Before executing the statement, the application sets the value of each data element in the bound array. When the statement is executed, the driver tries to process the entire array contents using one network roundtrip. For example, let us compare the following examples, Case 1 and Case 2.

rc = SQLPrepare (hstmt, "INSERT INTO DailyLedger (...) 
VALUES (?,?,...)", SQL_NTS); 
// bind parameters 
... 
do { 
// read ledger values into bound parameter buffers 
... 
rc = SQLExecute (hstmt); 
// insert row 
} while ! (eof);

SQLPrepare (hstmt, " INSERT INTO DailyLedger (...) VALUES 
(?,?,...)", SQL_NTS); 
SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMSET_SIZE, (UDWORD)100, 
SQL_IS_UINTEGER); 
SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, 
&rows_processed, SQL_IS_POINTER); 
// Specify an array in which to return the status of 
// each set of parameters. 
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, 
ParamStatusArray, SQL_IS_POINTER); 
// pass 100 parameters per execute 
// bind parameters 
... 
do { 
// read up to 100 ledger values into 
// bound parameter buffers 
... 
rc = SQLExecute (hstmt); 
// insert a group of 100 rows 
} while ! (eof);

In Case 1, if there are 100 rows to insert, 101 network roundtrips are required to the server, one to prepare the statement with SQLPrepare and 100 additional roundtrips for each time SQLExecute is called.

In Case 2, the call load has been reduced from 100 SQLExecute calls to only 1 SQLExecute call. Furthermore, network traffic is reduced considerably.

Using SQLPrepare and Multiple SQLExecute Calls

Applications that use SQLPrepare and multiple SQLExecute calls should use SQLParamOptions. Passing arrays of parameter values reduces the ODBC call load and network traffic.

rc = SQLPrepare (hstmt, "INSERT INTO DailyLedger (...) 
   VALUES (?,?,...)", SQL_NTS); 
// bind parameters 
... 
do { 
// read ledger values into bound parameter buffers 
... 
rc = SQLExecute (hstmt);      // insert row 
} while ! (eof);

If there are 100 rows to insert, SQLExecute is called 100 times, resulting in 100 network requests to the server.

Alternatively, consider an algorithm that uses parameter arrays by calling SQLParamOptions:

rc = SQLPrepare (hstmt, "INSERT INTO DailyLedger (...)  
   VALUES (?,?,...)", SQL_NTS); 
rc = SQLParamOptions (hstmt, (UDWORD) 50, &CurrentRow); 
// pass 50 parameters per execute 
// bind parameters 
... 
do { 
// read up to 50 ledger values into bound parameter buffers 
... 
rc = SQLExecute (hstmt);      // insert row

The call load is reduced from 100 to just 2 SQLExecute calls. Furthermore, network traffic is reduced considerably. To achieve the best performance, applications should contain algorithms for using SQLParamOptions. SQLParamOptions is ideal for copying data into new tables or bulk loading tables. Note, however, that some ODBC drivers do not support SQLParamOptions.

Using the Cursor Library

If the driver provides scrollable cursors, do not use the cursor library automatically. The cursor library creates local temporary log files, which are performance-expensive to generate and provide worse performance than native scrollable cursors.

The cursor library adds support for static cursors, which simplifies the coding of applications that use scrollable cursors. However, the cursor library creates temporary log files on the user's local disk drive as it performs the task. Typically, disk input/output is a slow operation. Although the cursor library is beneficial, applications should not choose automatically to use the cursor library when an ODBC driver supports scrollable cursors natively.

Typically, ODBC drivers that support scrollable cursors achieve better performance by requesting that the database server produce a scrollable result set, instead of emulating the capability by creating log files. Many applications use:

rc = SQLSetConnectOption (hdbc, SQL_ODBC_CURSORS, 
   SQL_CUR_USE_ODBC);

rc = SQLSetConnectOption (hdbc, SQL_ODBC_CURSORS, 
   SQL_CUR_USE_IF_NEEDED);